package com.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.BillingBean;

@Repository
public class BillingDao {
	
	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	/**
	 * 添加新的计费点
	 * @param billing
	 * @return
	 */
	public int insertBilling(BillingBean billing){
		final String sql = "insert into billing(billingName,fee,spendLimit,billingType,days,tipType,periodHour,confirmTimes,userType," +
				"tipMessage,memo,productId,productName)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {billing.getBillingName(),billing.getFee(),billing.getSpendLimit(),billing.getBillingType(),billing.getDays(),
				billing.getTipType(),billing.getPeriodHour(),billing.getConfirmTimes(),billing.getUserType(),
				billing.getTipMessage(),billing.getMemo(),billing.getProductId(),billing.getProductName()};
		return jdbcTemplate.update(sql, params);
	}
	
	/**
	 * 删除计费点
	 * @param billingId
	 * @return
	 */
	public int delBilling(int billingId){
		final String sql = "delete from billing where billingId = ?";
		return jdbcTemplate.update(sql, billingId);
	}
	
	
	/**
	 * 修改计费点
	 * @param billing
	 * @return
	 */
	public int updateBilling(BillingBean billing){
		final String sql = "update billing set billingName=?,fee=?,spendLimit=?,billingType=?,days=?,tipType=?,periodHour=?,confirmTimes=?,userType=?," +
			"tipMessage=?,memo=?,productId=?,productName=? where billingId = ?";
		Object[] params = {billing.getBillingName(),billing.getFee(),billing.getSpendLimit(),billing.getBillingType(),billing.getDays(),
				billing.getTipType(),billing.getPeriodHour(),billing.getConfirmTimes(),billing.getUserType(),
				billing.getTipMessage(),billing.getMemo(),billing.getProductId(),billing.getProductName(),billing.getBillingId()};
		return jdbcTemplate.update(sql, params);
	}
	
	/**
	 * 根据条件查询计费总数
	 * @param productId
	 * @param userType
	 * @return
	 */
	public int findCountByCondition(int productId,int userType){
		StringBuffer sql = new StringBuffer("select count(0) from billing where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(productId!=0){
			sql.append(" and productId = ?");
			params.add(productId);
		}
		if(userType>=0){
			sql.append(" and userType = ?");
			params.add(userType);
		}
		return jdbcTemplate.queryForInt(sql.toString(),params.toArray());
	}
	
	/**
	 * 根据条件查询计费点
	 * @param productId
	 * @param userType
	 * @param start
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public List<BillingBean> findBillingByCondition(int productId,int userType,int start,int pageSize) throws SQLException{
		StringBuffer sql = new StringBuffer("select * from billing where 1=1");
		List<Object> params = new ArrayList<Object>();
		if(productId!=0){
			sql.append(" and productId = ?");
			params.add(productId);
		}
		if(userType>=0){
			sql.append(" and userType = ?");
			params.add(userType);
		}
		sql.append(" order by createTime desc limit ?,?");
		params.add(start);
		params.add(pageSize);
		
		return queryRunner.query(sql.toString(), new BeanListHandler<BillingBean>(BillingBean.class), params.toArray());
	}
	
	/**
	 * 根据ID查询
	 * @param billingId
	 * @return
	 * @throws SQLException
	 */
	public BillingBean findBillingById(int billingId) throws SQLException{
		final String sql = "select * from billing where billingId = ?";
		return queryRunner.query(sql, new BeanHandler<BillingBean>(BillingBean.class), billingId);
	}
	
	/**
	 * 根据产品ID取计费点
	 * @param productId
	 * @return
	 * @throws SQLException
	 */
	public List<BillingBean> findBillingByProductId(int productId) throws SQLException{
		String sql = "select * from billing where productId = ?";
		return queryRunner.query(sql.toString(), new BeanListHandler<BillingBean>(BillingBean.class), productId);
	}
	
	/**
	 * 修改计费点中的产品名
	 * @param productId
	 * @return
	 */
	public int updateProductName(int productId,String productName){
		String sql = "update billing set productName=? where productId = ?";
		return jdbcTemplate.update(sql, new Object[]{productName,productId});
	}
}

